<?php
/**
 * @description : all case function of count.
 * @author : stephen.mo
 * @date : 2010-06-08
 */

class MstatInline {
    private $reader;
    private $writer;
    private $membership;

    static public function getInstance() {
        static $instance = null ;
        return $instance ? ($instance) : ($instance = new MstatInline()) ;
    }
    
    public function __construct() {
        $this->reader = DBConnPoolFactory::getInstance()->getConnPool(MstatInlineConfig::DATABASE_CONFIG_PATH)->getReadConn();
        $this->writer = DBConnPoolFactory::getInstance()->getConnPool(MstatInlineConfig::DATABASE_CONFIG_PATH)->getWriteConn();
        $this->membership = DBConnPoolFactory::getInstance()->getConnPool(MstatInlineConfig::MEMBERSIHP_CONFIG_FILE)->getReadConn();
    }

    //check the record is exists.
    public function checkRecordIsExists($day_time,$type) {
        $dbh = $this->writer;
        $query = "SELECT id FROM ".MstatInlineConfig::$mstat_inline_member_daily.
                 " WHERE type=".$type." AND day_time=:day_time ";
        $ps = $dbh->prepare($query);
        $ps->bindValue(':day_time',$day_time);
        $ret = $ps->execute();
        if (!$ret) {
            return -1;
        }
        return ($ps->rowCount()>0) ? true : false;
    }
    
    //count the login members.
    public function MSTATCountLogin($date_format,$hour=false) {
        $dbh = $this->writer;
        $time_format = (!$hour) ? '%Y-%m-%d' : '%Y-%m-%d %H';
        $sql = "SELECT * FROM ".MstatInlineConfig::$mstat_inline_member_access." WHERE DATE_FORMAT(login_time,'".$time_format."')=:date_format";
        $ps = $dbh->prepare($sql);
	    $ps->bindValue(':date_format', $date_format);
	    $ret = $ps->execute();
	    if(! $ret){
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(), 1));
            return FALSE;
        }
        $num = $ps->rowCount();
        $ps->closeCursor();
	    return $num;
    }

    //count the logout members.
    public function MSTATCountLogout($date_format,$hour=false) {
        $dbh = $this->writer;
        $time_format = (!$hour) ? '%Y-%m-%d' : '%Y-%m-%d %H';
        $sql = "SELECT * FROM ".MstatInlineConfig::$mstat_inline_member_access." WHERE DATE_FORMAT(logout_time,'".$time_format."')=:date_format";
        $ps = $dbh->prepare($sql);
        $ps->bindValue(':date_format',$date_format);
        $ret = $ps->execute();
        if (!$ret) {
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(), 1));
            return FALSE;
        }
        $num = $ps->rowCount();
        $ps->closeCursor();
        return $num;
    }

    //count the active members.check data from membership.
    public function MSTATCountActivation($date_format,$hour=false) {
        $dbh = $this->membership;
        $time_format = (!$hour) ? '%Y-%m-%d' : '%Y-%m-%d %H';
        $sql = "SELECT * FROM member_profile WHERE DATE_FORMAT(activation_date,'{$time_format}')=:date_format";
        $ps = $dbh->prepare($sql);
        $ps->bindValue('date_format', $date_format);
        $ret = $ps->execute();
        if(! $ret){
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(), 1));
            return FALSE;
        }
        $num = $ps->rowCount();
        $ps->closeCursor();
        return $num;
    }

    //count confirm.
    public function MSTATCountConfirm($date_format,$hour=false) {
        $dbh = $this->membership;
        $time_format = (!$hour) ? '%Y-%m-%d' : '%Y-%m-%d %H';
        $sql = "SELECT * FROM member_profile WHERE DATE_FORMAT(confirm_date,'{$time_format}')=:date_format";
        $ps = $dbh->prepare($sql);
        $ps->bindValue('date_format', $date_format);
        $ret = $ps->execute();
        if(!$ret){
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(), 1));
            return FALSE;
        }
        $num = $ps->rowCount();
        $ps->closeCursor();
        return $num;
    }

    //count register.
    public function MSTATCountRegister($date_format,$hour=false) {
        $dbh = $this->membership;
        $time_format = (!$hour) ? '%Y-%m-%d' : '%Y-%m-%d %H';
        $sql = "SELECT * FROM member_profile WHERE DATE_FORMAT(join_date,'{$time_format}')=:date_format";
        $ps = $dbh->prepare($sql);
        $ps->bindValue('date_format', $date_format);
        $ret = $ps->execute();
        if(!$ret) {
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(), 1));
            return FALSE;
        }
        $num = $ps->rowCount();
        $ps->closeCursor();
        return $num;
    }

    //count MSTATCountExpired.
    public function MSTATCountExpired($date_format) {
        $dbh = $this->membership;
        //fix the time stamp in php bin mode need add 8*60*60
        //$date_time_stamp = strtotime($date_format)+8*60*60;
        $date_time_stamp = strtotime($date_format);
        $expiredDay = 7*24*60*60;
        $expriedDay_end = 8*24*60*60;
        //$sql = "SELECT * FROM member_profile WHERE {$date_time_stamp}-UNIX_TIMESTAMP(join_date + INTERVAL 8 HOUR)>={$expiredDay} and {$date_time_stamp}-UNIX_TIMESTAMP(join_date + INTERVAL 8 HOUR)<={$expriedDay_end} and confirm_date is null and status=0";
        $sql = "SELECT * FROM member_profile WHERE {$date_time_stamp}-UNIX_TIMESTAMP(join_date)>={$expiredDay} AND {$date_time_stamp}-UNIX_TIMESTAMP(join_date)<={$expriedDay_end} AND confirm_date is null AND status=0";
        $ps = $dbh->prepare($sql);
        $ret = $ps->execute();
        if(!$ret) {
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(), 1));
            return FALSE;
        }
        $num = $ps->rowCount();
        $ps->closeCursor();
        return $num;
    }

    //count MSTATCountExpiredHour.
    public function MSTATCountExpiredHour($date_format,$hour) {
        $dbh = $this->membership;
        $date_time_stamp = strtotime($date_format);
        $expiredDay = 7*24*60*60;
        $expriedDay_end = 8*24*60*60;
        //$sql = "SELECT * FROM member_profile WHERE {$date_time_stamp}-UNIX_TIMESTAMP(join_date + INTERVAL 8 HOUR)>={$expiredDay} AND {$date_time_stamp}-UNIX_TIMESTAMP(join_date + INTERVAL 8 HOUR)<={$expriedDay_end} AND HOUR(join_date)='{$hour}' AND confirm_date is null AND status=0";
        $sql = "SELECT * FROM member_profile WHERE {$date_time_stamp}-UNIX_TIMESTAMP(join_date)>={$expiredDay} AND {$date_time_stamp}-UNIX_TIMESTAMP(join_date)<={$expriedDay_end} AND HOUR(join_date)='{$hour}' AND confirm_date is null AND status=0";
        $ps = $dbh->prepare($sql);
        $ret = $ps->execute();
        if(!$ret) {
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(), 1));
            return FALSE;
        }
        $num = $ps->rowCount();
        $ps->closeCursor();
        return $num;
    }
    
    //count confirm expired.
    public function MSTATCountConfirmExpired($date_format) {
        $dbh = $this->membership;
        // fix the time stamp in php bin mode need add 8*60*60
        $sql = "SELECT * FROM member_profile WHERE  DATE_FORMAT(join_date,'%Y-%m-%d')=:date_format AND status=4";
        $ps = $dbh->prepare($sql);
        $ps->bindValue('date_format', $date_format);
        $ret = $ps->execute();
        if(!$ret) {
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(), 1));
            return FALSE;
        }
        $num = $ps->rowCount();
        $ps->closeCursor();
        return $num;
    }

    //count confirm expired with hour.
    public function MSTATCountConfirExpiredHour($date_format,$hour) {
        $dbh = $this->membership;
        $sql = "SELECT * FROM member_profile WHERE DATE_FORMAT(join_date,'%Y-%m-%d')=:date_format AND HOUR(join_date)='{$hour}' AND status=4";
        $ps = $dbh->prepare($sql);
        $ps->bindValue('date_format', $date_format);
        $ret = $ps->execute();
        if(!$ret){
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(), 1));
            return FALSE;
        }
        $num = $ps->rowCount();
        $ps->closeCursor();
        return $num;
    }

    //expired list.
    public function MSTATGetExpiredRecord($date_format) {
        $dbh = $this->membership;
        $date_format_stamp = strtotime($date_format);
        $month_day_nums = date("t",$date_format_stamp);
        $cur_year = date("Y",$date_format_stamp);
        $cur_day = date("d",$date_format_stamp);
        $cur_month = date("m",$date_format_stamp);
        $loop_nums = ($cur_day < $month_day_nums) ? $cur_day : $month_day_nums;
        $sql = "";
        for($ik=1;$ik<=$loop_nums;$ik++){
            $c_day = ($ik<10) ? "0".$ik:$ik;
            $date_new_format = $cur_year."-".$cur_month."-".$c_day." 00:00:00";
            // fix the time stamp in php bin mode need add 8*60*60
            $date_time_stamp = strtotime($date_new_format);

            $expiredDay = 7*24*60*60;
            $expriedDay_end = 8*24*60*60;
            if($sql != "") $sql .= " UNION ";
            //$sql .= "SELECT login_id,email,first_name,last_name FROM member_profile WHERE {$date_time_stamp}-UNIX_TIMESTAMP(join_date + INTERVAL 8 HOUR)>={$expiredDay} and {$date_time_stamp}-UNIX_TIMESTAMP(join_date + INTERVAL 8 HOUR)<={$expriedDay_end} and confirm_date is null and status=0";
            $sql .= "SELECT * FROM member_profile WHERE {$date_time_stamp}-UNIX_TIMESTAMP(join_date)>={$expiredDay} AND {$date_time_stamp}-UNIX_TIMESTAMP(join_date)<={$expriedDay_end} AND confirm_date is null AND status=0";
        }
        $ps = $dbh->prepare($sql);
        $ret = $ps->execute();
        if(!$ret) {
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(), 1));
            return FALSE;
        }
        $result = $ps->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }

    //





    //public methods .
    //insert new record to daily_member_log table.
    public function MSTATLogDayInsert($date_format, $total, $data, $type) {
        $dbh = $this->writer;
        $dbh->beginTransaction();
        $hour_format = '';
        $hour_value = '';
        for($i=0;$i<24;$i++) {
            if($i==23) {
                $hour_format.='hour'.$i;
                $hour_value.=':hour'.$i;
            } else {
                $hour_format.='hour'.$i.",";
                $hour_value.=':hour'.$i.",";
            }
        }

        $sql = "REPLACE INTO ".MstatInlineConfig::$mstat_inline_member_daily.
               " (type,day_time,day_count,create_time,$hour_format) ".
               " VALUES ".
               " (:type,:day_time,:day_count,:create_time,$hour_value) ";
               //" WHERE type=:type AND day_time=:day_time ";
		$ps = $dbh->prepare($sql);
        $ps->bindValue(':type', $type);
        $ps->bindValue(':day_time', $date_format);
        $ps->bindValue(':day_count', $total);
	    $ps->bindValue(':create_time', date("Y-m-d h:i:s",time()));
	    for($i=0;$i<24;$i++) {
		    $ps->bindValue('hour'.$i, $data[$i]['hour']);
	    }
	    $ret = $ps->execute();
        if(!$ret){
            error_log("File '" . __FILE__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(), 1));
            $dbh->rollBack();
            return FALSE;
        }
        $id = $dbh->lastInsertId();
        $ps->closeCursor();
        $dbh->commit();
        return $id;
    }
    
    //update the record to daily_member_log table.
    public function MSTATLogDayUpdate($date_format,$total,$data,$type) {
        $dbh = $this->writer;
        $dbh->beginTransaction();
        $hour_format = '';
        $hour_value = '';
        for($i=0;$i<24;$i++) {
            $hour_format .= 'hour'.$i.'=:hour'.$i.',';
        }

        $query = "UPDATE ".MstatInlineConfig::$mstat_inline_member_daily.
                 " SET ".$hour_format." day_count=:day_count,create_time=:create_time ".
                 " WHERE type=:type AND day_time=:day_time ";
        $ps = $dbh->prepare($query);
        $ps->bindValue(':day_count',$total);
        $ps->bindValue(':create_time',date('Y-m-d h:i:s'));
        $ps->bindValue(':type',$type);
        $ps->bindValue(':day_time',trim($date_format));
        for($i=0;$i<24;$i++) {
		    $ps->bindValue(':hour'.$i, $data[$i]['hour']);
	    }
        $ret = $ps->execute();
        if(!$ret){
            error_log("File '" . __FILE__ . "', " . "Line " . __LINE__ . ": " . print_r($ps->errorInfo(), 1));
            $dbh->rollBack();
            return FALSE;
        }
        $ps->closeCursor();
        $dbh->commit();
        return true;
    }

	
	//Others for mstat_inline.
	//check the records is exists on mstat_inline_membership table.
	public function checkIsExistsOnMstatInline($type,$day_time) {
		$dbh = $this->writer;
		$query = "SELECT id FROM ".MstatInlineConfig::$mstat_inline_membership.
				 " WHERE type=:type AND day_time=:day_time ".
				 " LIMIT 1";
		$ps = $dbh->prepare($query);
		$ps->bindValue(':type',$type);
		$ps->bindValue(':day_time',$day_time);
		$ret = $ps->execute();
		if (!$ret) {
			error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(),1));
			return FALSE;
		}
		return ($ps->rowCount()>0) ? true : false;
	}


	//count the member sign-in or sign-out by country.
	public function MSTATCountCountry($date_time) {
	 	//$data = array();	
		$dbh = $this->writer;
		$query = "SELECT country,count(id) AS Total From ".MstatInlineConfig::$mstat_inline_member_access.
				 " WHERE SUBSTRING(login_time,1,10)=:date_time ".
				 " GROUP BY country ";
		$ps = $dbh->prepare($query);
		$ps->bindValue(':date_time',$date_time);
		$ret = $ps->execute();
		if (!$ret) {
			error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(),1));
			return FALSE;
		}
		$result = ($ps->rowCount()>0) ? $ps->fetchAll(PDO::FETCH_ASSOC) : null;
		return $result;			
	} 
	
	//count the member is forgot password	
	public function MSTATCountForget($date_time) {
		$dbh = $this->membership;
		$query = "SELECT * FROM password_enquiry_log WHERE DATE_FORMAT(enquiry_date,'%Y-%m-%d')=:date_format ";
		$ps = $dbh->prepare($query);
		$ps->bindValue(':date_format',$date_time);
		$ret = $ps->execute();
		if (!$ret) {
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(),1));
            return FALSE;
		}
		$num = $ps->rowCount();
		$ps->closeCursor();
		return $num;
	}

	//count how many people was edit profile.
	public function MSTATCountEdit($date_time) {
		$dbh = $this->membership;
		$query = "SELECT id FROM member_profile WHERE DATE_FORMAT(last_update_date,'%Y-%m-%d')=:date_time";
		$ps = $dbh->prepare($query);
		$ps->bindValue(':date_time',$date_time);
        $ret = $ps->execute();
        if (!$ret) {
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(),1));
            return FALSE;
        }
        $num = $ps->rowCount();
        $ps->closeCursor();
        return $num;
	}
	
	
	//count the old member & new member & oldmember active.
	public function MSTATOldMemberCount($date_time) {
		$dbh = $this->membership;
		$data = array();
		$query = "SELECT count(id) as Total FROM member_profile WHERE DATE_FORMAT(confirm_date,'%Y-%m-%d')<:date_time";
		$ps = $dbh->prepare($query);
		$ps->bindValue(':date_time',$date_time);
		$ret = $ps->execute();
		if (!$ret) {
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(),1));
            return FALSE;		
		}
		$result = $ps->fetchAll(PDO::FETCH_ASSOC);
		$data['old_member'] = (int)$result[0]['Total']; 
		
		$query = "SELECT count(id) as Total FROM member_profile WHERE DATE_FORMAT(confirm_date,'%Y-%m-%d')<:date_time AND status=2";
		$ps = $dbh->prepare($query);
		$ps->bindValue(':date_time',$date_time);
		$ret = $ps->execute();
		if (!$ret) {
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(),1));
            return FALSE;
		}
		$result = $ps->fetchAll(PDO::FETCH_ASSOC);
		$data['old_member_active'] = $result[0]['Total'];
		
		$query = "SELECT count(id) as Total FROM member_profile";
		$ps = $dbh->prepare($query);
		$ret = $ps->execute();
        if (!$ret) {
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(),1));
            return FALSE;
        }
        $result = $ps->fetchAll(PDO::FETCH_ASSOC);
        $data['all_member'] = $result[0]['Total'];
		
		$query = "SELECT count(id) as Total FROM member_profile WHERE DATE_FORMAT(confirm_date,'%Y-%m-%d')>=:date_time";
        $ps = $dbh->prepare($query);
        $ps->bindValue(':date_time',$date_time);
        $ret = $ps->execute();
        if (!$ret) {
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(),1));
            return FALSE;
        }
        $result = $ps->fetchAll(PDO::FETCH_ASSOC);
        $data['new_member'] = $result[0]['Total'];

		$query = "SELECT count(id) as Total FROM member_profile WHERE DATE_FORMAT(confirm_date,'%Y-%m-%d')>=:date_time AND status=2";
        $ps = $dbh->prepare($query);
        $ps->bindValue(':date_time',$date_time);
        $ret = $ps->execute();
        if (!$ret) {
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(),1));
            return FALSE;
        }
        $result = $ps->fetchAll(PDO::FETCH_ASSOC);
        $data['new_member_login'] = $result[0]['Total'];
		
		//new register.
		$data['new_registration'] = 0;

		//new register no confirm.	
		$data['registered_no_confirm'] = 0;

		return $data;	
	}

	//count how many member is detail inputs
	public function MSTATCountInputDetail ($date_time) {
		$dbh = $this->membership;
		$query = "SELECT count(id) as Total FROM member_profile ".
				 " WHERE DATE_FORMAT(last_update_date,'%Y-%m-%d')=DATE_FORMAT(join_date,'%Y-%m-%d') ".
				 " AND ".
				 " (first_name is not null OR ".
				 "last_name is not null OR ".
				 "phone is not null OR ".
				 "address is not null OR ".
				 "education !=0 OR ".
				 "income !=0 OR ".
				 "interest is not null) ".
			     "AND DATE_FORMAT(last_update_date,'%Y-%m-%d')=:date_time";
		$ps = $dbh->prepare($query);
		$ps->bindValue(':date_time',$date_time);
		$ret = $ps->execute();
		if (!$ret) {
            error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(),1));
            return FALSE;
		}
		$result = $ps->fetchAll(PDO::FETCH_ASSOC);
		$num = (int)$result[0]['Total'];
		return $num;
	}



	
	//insert a new record to mstat_inline_membership;
	public function MSTATInlineInsert($type,$day_time,$data) {
		$dbh = $this->writer;
		$dbh->beginTransaction();
		$query = "INSERT INTO ".MstatInlineConfig::$mstat_inline_membership.
				 " (id,type,day_time,data) ".
				 " VALUES ".
				 " (0,:type,:day_time,:data) ";
		$ps = $dbh->prepare($query);
		$ps->bindValue(':type',$type);
		$ps->bindValue(':day_time',$day_time);
		$ps->bindValue(':data',$data);
		$ret = $ps->execute();
		if (!$ret) {
			error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(),1));
			$dbh->rollBack();
			return FALSE;
		}
		$ps->closeCursor();
		$dbh->commit();	
		return true;	
	}

	//update a record to mstat_inline_membership;
	public function MSTATInlineUpdate($type,$day_time,$data) {
		$dbh = $this->writer;
		$dbh->beginTransaction();
		$query = "UPDATE ".MstatInlineConfig::$mstat_inline_membership.
				 " SET data=:data ".
				 " WHERE type=:type AND day_time=:day_time ";
		$ps = $dbh->prepare($query);
		$ps->bindValue(':data',$data);
		$ps->bindValue(':type',$type);
		$ps->bindValue(':day_time',$day_time);
		$ret = $ps->execute();
		if (!$ret) {
			error_log("File '".__FILE__."', "."Line ".__LINE__.": ".print_r($ps->errorInfo(),1));
			$dbh->rollBack();
			return FALSE;
		}
		$ps->closeCursor();
		$dbh->commit();
		return true;
	}



}

?>
